use once_cell::sync::OnceCell;
use serde::{Deserialize, Serialize};
use sqlx::{sqlite::SqlitePoolOptions, Pool, Sqlite, SqlitePool};

// 创建一个全局的DB_POOL，可以一直使用，启动的时候初始化即可
static DB_POOL: OnceCell<Pool<Sqlite>> = OnceCell::new();

/// 当前数据库 schema 的最新版本号（你手动维护）
const CURRENT_VERSION: i32 = 4;

// 初始化数据库链接池
pub async fn init_mysql_pool(db_url: &str, path: String) {
    log::info!("初始化数据库线程池--------开始-------");
    log::info!("地址：{}", path);

    if let Ok(metadata) = std::fs::metadata(path.clone() + "\\" + db_url) {
        if !metadata.is_file() {
            let _ = std::fs::create_dir_all(path.clone());
            let _ = std::fs::File::create(path.clone() + "\\" + db_url);
        }
    } else {
        let _ = std::fs::create_dir_all(path.clone());
        let _ = std::fs::File::create(path.clone() + "\\" + db_url);
    }

    let pool = SqlitePoolOptions::new()
        .connect_lazy(&("sqlite:".to_string() + &path + "\\" + db_url))
        .ok()
        .expect("链接失败");

    //判断数据库是否需要升级
    let _ = upgrade_database(&pool).await;

    DB_POOL
        .set(pool)
        .unwrap_or_else(|_| println!("try insert pool cell failure!"));
    log::info!("初始化数据库线程池--------结束-------");
}

// 从链接链接池里面获取链接

pub fn get_connect() -> Pool<Sqlite> {
    //println!("从链接池获取数据库链接----------开始----------");
    let conn = DB_POOL
        .get()
        .expect("Error get pool from OneCell<Pool>")
        .clone();
    //println!("从链接池获取数据库链接----------结束----------");
    conn
}

#[derive(Serialize, Deserialize)]
pub struct Timer {
    time: String,
    id: i64,
}

pub async fn upgrade_database(pool: &SqlitePool) -> Result<(), sqlx::Error> {
    // 获取当前数据库版本
    let mut version: i32 = sqlx::query_scalar("PRAGMA user_version")
        .fetch_one(pool)
        .await
        .unwrap_or(0); // 默认 0 表示未设置过
    log::info!("查询的版本 {}", version);
    let ver = version;
    while version < CURRENT_VERSION {
        match version {
            0 => {
                println!("Migrating DB from v0 to v1...");
                init(pool).await?;
                version = 1;
            }
            1 => {
                println!("Migrating DB from v1 to v2...");
                version = 2;
            }
            2 => {
                println!("Migrating DB from v2 to v3...");
                version = 3;
            }
            3 => {
                println!("Migrating DB from v3 to v4...");
                version = 4;
            }
            4 => {
                println!("Migrating DB from v4 to v5...");
                version = 4;
            }
            _ => {
                panic!("Unknown DB version: {}", version);
                break;
            }
        }
    }

    if ver <= CURRENT_VERSION {
         log::info!("更新版本 {}", version);
        // 更新数据库版本
        sqlx::query(&format!("PRAGMA user_version = {}", version))
            .execute(pool)
            .await?;
    }

    Ok(())
}

async fn init(pool: &SqlitePool) -> Result<(), sqlx::Error> {
    // 初始建表
    sqlx::query(
        r#"
CREATE TABLE "book_name" (
  "id" TEXT NOT NULL,
  "title" TEXT,
  "content" TEXT,
  "use_time" integer,
  "create_time" integer,
  "creator_lid" TEXT,
  "creator_name" TEXT,
  "updater_lid" TEXT,
  "updater_name" TEXT,
  "up_ver" integer,
  "sort" integer,
  "tenant_id" TEXT,
  "deleted" integer DEFAULT 0,
  "update_time" integer,
  PRIMARY KEY ("id")
);

CREATE INDEX "idx_book_name_deleted"
ON "book_name" (
  "deleted" ASC
);

CREATE INDEX "idx_book_name_use_time"
ON "book_name" (
  "use_time" DESC
);


CREATE TABLE "momento" (
  "id" TEXT NOT NULL,
  "pids" TEXT,
  "pid" text,
  "book_id" text,
  "is_folder" integer DEFAULT 1,
  "title" TEXT,
  "heading" TEXT,
  "preview" TEXT,
  "file_path" TEXT,
  "note_content" TEXT,
  "date" integer,
  "group_id" TEXT,
  "files" TEXT,
  "open_type" TEXT,
  "add_date" text,
  "create_time" integer,
  "creator_lid" TEXT,
  "creator_name" TEXT,
  "updater_lid" TEXT,
  "updater_name" TEXT,
  "up_ver" integer,
  "sort" integer,
  "tenant_id" TEXT,
  "deleted" integer DEFAULT 0,
  "update_time" integer,
  PRIMARY KEY ("id")
);

CREATE INDEX "idx_momento_create_time_copy1"
ON "momento" (
  "create_time" DESC
);

CREATE INDEX "idx_momento_creator_lid_copy1"
ON "momento" (
  "creator_lid" ASC
);

CREATE INDEX "idx_momento_date_copy1"
ON "momento" (
  "date" DESC
);

CREATE INDEX "idx_momento_group_type_copy1"
ON "momento" (
  "group_id" ASC
);

CREATE INDEX "idx_momento_not_deleted_copy1"
ON "momento" (
  "deleted" ASC
);


CREATE TABLE "config" (
  "id" TEXT NOT NULL,
  "dic_path" TEXT,
  "gitee" TEXT,
  "create_time" integer,
  "creator_lid" TEXT,
  "creator_name" TEXT,
  "updater_lid" TEXT,
  "updater_name" TEXT,
  "up_ver" integer,
  "sort" integer,
  "tenant_id" TEXT,
  "deleted" integer DEFAULT 0,
  "update_time" integer,
  PRIMARY KEY ("id")
);


INSERT INTO "main"."config" ("id", "dic_path", "gitee", "create_time", "creator_lid", "creator_name", "updater_lid", "updater_name", "up_ver", "sort", "tenant_id", "deleted", "update_time") VALUES ('1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL);


    "#,
    )
    .execute(pool)
    .await
    .expect("数据库初始化失败！");
    log::info!("初始化成功！");
    Ok(())
}
